All of the temporary tables are stored in the tempdb database and can be listed using the query below.
SELECT *
FROM tempdb.sys.objects
WHERE name like '#%';
All users in SQL Server can execute the query above, but the access users have to the tables displayed depends largely on the table type and scope.
Below is a summary of the scope for each type of temporary table.
With that foundation in place, let’s walk through some TSQL exercises to help better understand each of those scope boundaries.
Exercise 1: Table Variables
Table variables are limited to a single query batch within the current user’s active session. They’re not accessible to other query batches, or to other active user sessions. As a result, it’s not very likely that data would be leaked to unprivileged users.
Below is an example of referencing a table variable in the same batch.
-- Create table variable
If not Exists (SELECT name FROM tempdb.sys.objects WHERE name = 'table_variable')
DECLARE @table_variable TABLE (Spy_id INT NOT NULL, SpyName text NOT NULL, RealName text NULL);
-- Insert records into table variable
INSERT INTO @table_variable (Spy_id, SpyName, RealName) VALUES (1,'Black Widow','Scarlett Johansson')
INSERT INTO @table_variable (Spy_id, SpyName, RealName) VALUES (2,'Ethan Hunt','Tom Cruise')
INSERT INTO @table_variable (Spy_id, SpyName, RealName) VALUES (3,'Evelyn Salt','Angelina Jolie')
INSERT INTO @table_variable (Spy_id, SpyName, RealName) VALUES (4,'James Bond','Sean Connery')
-- Query table variable in same batch
SELECT *
FROM @table_variable
GO
We can see from the image above that we are able to query the table variable within the same batch query. However, when we separate the table creation and table data selection into two batches using “GO”, we can see that the table variable is no longer accessible outside of its original batch job. Below is an example.
Hopefully that helps illustrate the scope limitations of table variables, but you might still be wondering how they’re stored. When you create a table variable it’s stored in tempdb using a name starting with a “#” and randomly generated characters. The query below can be used to filter for table variables being used.
SELECT *
FROM tempdb.sys.objects
WHERE name not like '%[_]%'
AND (select len(name) - len(replace(name,'#',''))) = 1
